const { query } = require('../config/db');

class Category {
  /**
   * 根据ID查找分类
   * @param {number} id - 分类ID
   * @returns {Promise<Object|null>} 分类信息
   */
  static async findById(id) {
    try {
      const categories = await query(
        'SELECT * FROM categories WHERE id = ?',
        [id]
      );
      return categories.length > 0 ? categories[0] : null;
    } catch (error) {
      console.error('查找分类失败:', error);
      throw error;
    }
  }

  /**
   * 根据名称和父分类查找分类
   * @param {string} name - 分类名称
   * @param {number} parentId - 父分类ID
   * @returns {Promise<Object|null>} 分类信息
   */
  static async findByNameAndParent(name, parentId = 0) {
    try {
      const categories = await query(
        'SELECT * FROM categories WHERE name = ? AND parent_id = ?',
        [name, parentId]
      );
      return categories.length > 0 ? categories[0] : null;
    } catch (error) {
      console.error('查找分类失败:', error);
      throw error;
    }
  }

  /**
   * 创建分类
   * @param {Object} categoryData - 分类数据
   * @returns {Promise<Object>} 创建的分类信息
   */
  static async create(categoryData) {
    try {
      const { parent_id, name, icon_url, sort_order } = categoryData;
      
      const result = await query(
        'INSERT INTO categories (parent_id, name, icon_url, sort_order) VALUES (?, ?, ?, ?)',
        [parent_id || 0, name, icon_url, sort_order || 99]
      );

      return await this.findById(result.insertId);
    } catch (error) {
      console.error('创建分类失败:', error);
      throw error;
    }
  }

  /**
   * 更新分类
   * @param {number} id - 分类ID
   * @param {Object} updateData - 更新数据
   * @returns {Promise<Object>} 更新后的分类信息
   */
  static async update(id, updateData) {
    try {
      const fields = [];
      const values = [];

      Object.keys(updateData).forEach(key => {
        if (updateData[key] !== undefined) {
          fields.push(`${key} = ?`);
          values.push(updateData[key]);
        }
      });

      if (fields.length === 0) {
        throw new Error('没有要更新的字段');
      }

      values.push(id);
      await query(
        `UPDATE categories SET ${fields.join(', ')} WHERE id = ?`,
        values
      );

      return await this.findById(id);
    } catch (error) {
      console.error('更新分类失败:', error);
      throw error;
    }
  }

  /**
   * 删除分类
   * @param {number} id - 分类ID
   * @returns {Promise<boolean>} 删除结果
   */
  static async delete(id) {
    try {
      // 检查是否有子分类
      const children = await query(
        'SELECT COUNT(*) as count FROM categories WHERE parent_id = ?',
        [id]
      );

      if (children[0].count > 0) {
        throw new Error('该分类下还有子分类，无法删除');
      }

      // 检查是否有商品使用该分类
      const products = await query(
        'SELECT COUNT(*) as count FROM products WHERE category_id = ?',
        [id]
      );

      if (products[0].count > 0) {
        throw new Error('该分类下还有商品，无法删除');
      }

      const result = await query('DELETE FROM categories WHERE id = ?', [id]);
      return result.affectedRows > 0;
    } catch (error) {
      console.error('删除分类失败:', error);
      throw error;
    }
  }

  /**
   * 获取所有分类（树形结构）
   * @returns {Promise<Array>} 分类树
   */
  static async getTree() {
    try {
      const categories = await query(
        'SELECT * FROM categories ORDER BY parent_id ASC, sort_order ASC, id ASC'
      );

      // 构建树形结构
      const categoryMap = new Map();
      const tree = [];

      // 先创建所有节点的映射
      categories.forEach(category => {
        categoryMap.set(category.id, {
          ...category,
          children: []
        });
      });

      // 构建树形结构
      categories.forEach(category => {
        const node = categoryMap.get(category.id);
        if (category.parent_id === 0) {
          tree.push(node);
        } else {
          const parent = categoryMap.get(category.parent_id);
          if (parent) {
            parent.children.push(node);
          }
        }
      });

      return tree;
    } catch (error) {
      console.error('获取分类树失败:', error);
      throw error;
    }
  }

  /**
   * 获取分类列表（扁平结构）
   * @param {Object} options - 查询选项
   * @returns {Promise<Object>} 分类列表和总数
   */
  static async getList(options = {}) {
    try {
      const {
        page = 1,
        pageSize = 10,
        search = '',
        parent_id = null
      } = options;

      const offset = (page - 1) * pageSize;
      let whereClause = 'WHERE 1=1';
      let params = [];

      if (search) {
        whereClause += ' AND name LIKE ?';
        params.push(`%${search}%`);
      }

      if (parent_id !== null) {
        whereClause += ' AND parent_id = ?';
        params.push(parent_id);
      }

      // 获取总数
      const countResult = await query(
        `SELECT COUNT(*) as total FROM categories ${whereClause}`,
        params
      );
      const total = countResult[0].total;

      // 获取列表
      const categories = await query(`
        SELECT 
          c.*,
          p.name as parent_name,
          (SELECT COUNT(*) FROM categories WHERE parent_id = c.id) as children_count,
          (SELECT COUNT(*) FROM products WHERE category_id = c.id) as product_count
        FROM categories c
        LEFT JOIN categories p ON c.parent_id = p.id
        ${whereClause}
        ORDER BY c.parent_id ASC, c.sort_order ASC, c.id ASC
        LIMIT ? OFFSET ?
      `, [...params, pageSize, offset]);

      return { categories, total };
    } catch (error) {
      console.error('获取分类列表失败:', error);
      throw error;
    }
  }

  /**
   * 获取顶级分类
   * @returns {Promise<Array>} 顶级分类列表
   */
  static async getTopLevel() {
    try {
      const categories = await query(
        'SELECT * FROM categories WHERE parent_id = 0 ORDER BY sort_order ASC, id ASC'
      );
      return categories;
    } catch (error) {
      console.error('获取顶级分类失败:', error);
      throw error;
    }
  }

  /**
   * 获取子分类
   * @param {number} parentId - 父分类ID
   * @returns {Promise<Array>} 子分类列表
   */
  static async getChildren(parentId) {
    try {
      const categories = await query(
        'SELECT * FROM categories WHERE parent_id = ? ORDER BY sort_order ASC, id ASC',
        [parentId]
      );
      return categories;
    } catch (error) {
      console.error('获取子分类失败:', error);
      throw error;
    }
  }

  /**
   * 获取分类路径
   * @param {number} id - 分类ID
   * @returns {Promise<Array>} 分类路径
   */
  static async getPath(id) {
    try {
      const path = [];
      let currentId = id;

      while (currentId && currentId !== 0) {
        const category = await this.findById(currentId);
        if (!category) break;
        
        path.unshift(category);
        currentId = category.parent_id;
      }

      return path;
    } catch (error) {
      console.error('获取分类路径失败:', error);
      throw error;
    }
  }

  /**
   * 更新排序
   * @param {Array} sortData - 排序数据 [{id, sort_order}, ...]
   * @returns {Promise<boolean>} 更新结果
   */
  static async updateSort(sortData) {
    try {
      for (const item of sortData) {
        await query(
          'UPDATE categories SET sort_order = ? WHERE id = ?',
          [item.sort_order, item.id]
        );
      }
      return true;
    } catch (error) {
      console.error('更新分类排序失败:', error);
      throw error;
    }
  }
}

module.exports = Category;
